import akshare as ak
import pandas as pd
import numpy as np
from datetime import datetime, date, timedelta
from sqlalchemy import create_engine, text
import time
from functools import wraps
from db_config import DBConfig

# 简单的重试装饰器实现
def retry(max_attempts=5, delay=1, backoff=2, exceptions=(Exception,)):
    """
    简单的重试装饰器
    :param max_attempts: 最大重试次数
    :param delay: 初始延迟时间（秒）
    :param backoff: 退避系数
    :param exceptions: 需要重试的异常类型
    """
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            attempts = 0
            current_delay = delay
            while attempts < max_attempts:
                try:
                    return func(*args, **kwargs)
                except exceptions as e:
                    attempts += 1
                    if attempts == max_attempts:
                        print(f"函数 {func.__name__} 重试 {max_attempts} 次后仍然失败: {e}")
                        raise
                    print(f"函数 {func.__name__} 第 {attempts} 次失败: {e}, {current_delay}秒后重试...")
                    time.sleep(current_delay)
                    current_delay *= backoff  # 指数退避
        return wrapper
    return decorator

def clean_stock_code(stock_code):
    """
    清理股票代码，移除.sz和.sh后缀
    :param stock_code: 原始股票代码
    :return: 清理后的股票代码
    """
    return stock_code.replace('.SZ', '').replace('.SH', '').replace('.BJ', '')

def calculate_next_report_expected(last_report_date):
    """
    根据国内A股财报披露规则计算下一个预计报告期
    :param last_report_date: 最新财报日期
    :return: 下一个预计报告期
    """
    if not last_report_date:
        return None
        
    # 转换为日期对象
    if isinstance(last_report_date, str):
        last_report_date = datetime.strptime(last_report_date, "%Y-%m-%d").date()
    
    year = last_report_date.year
    month = last_report_date.month
    day = last_report_date.day
    
    # 根据财报季度确定下一个报告期
    if month == 3 and day == 31:  # Q1 → 半年报
        return date(year, 6, 30)
    elif month == 6 and day == 30:  # 半年报 → Q3
        return date(year, 9, 30)
    elif month == 9 and day == 30:  # Q3 → 年报
        return date(year, 12, 31)
    elif month == 12 and day == 31:  # 年报 → 下一年的Q1
        return date(year + 1, 3, 31)
    else:
        # 如果不是标准季度末日期，尝试根据月份推断
        if month <= 3:
            return date(year, 3, 31)
        elif month <= 6:
            return date(year, 6, 30)
        elif month <= 9:
            return date(year, 9, 30)
        else:
            return date(year, 12, 31)

def sync_stock_finance_to_db(stock_code, stock_name):
    """
    同步股票财务数据到数据库
    """
    try:
        # 清理股票代码
        clean_code = clean_stock_code(stock_code)
        
        # 获取股票财务数据
        raw_df = get_stock_finance_data(stock_code)
        if raw_df.empty:
            # 更新元数据表，记录错误信息
            update_finance_metadata_on_error(clean_code, stock_name, f"{stock_name}({clean_code}) 未获取到财务数据")
            return {
                "success": True,
                "message": f"{stock_name}({clean_code}) 未获取到财务数据",
                "records": 0
            }
        
        # 预处理财务数据
        df = prepare_stock_finance_data(raw_df, clean_code, stock_name)
        if df.empty:
            # 更新元数据表，记录错误信息
            update_finance_metadata_on_error(clean_code, stock_name, f"{stock_name}({clean_code}) 财务数据处理后无有效数据")
            return {
                "success": True,
                "message": f"{stock_name}({clean_code}) 财务数据处理后无有效数据",
                "records": 0
            }
        
        # 插入数据库
        records = insert_stock_finance_to_mysql(df)
        
        # 获取最新的报告日期和公告日期
        latest_report_date = df['report_date'].max()
        latest_notice_date = df[df['report_date'] == latest_report_date]['notice_date'].iloc[0] if not df[df['report_date'] == latest_report_date].empty else None
        
        # 计算下一个预计报告期
        next_report_expected = calculate_next_report_expected(latest_report_date)
        
        # 更新元数据表
        update_finance_metadata_on_success(
            clean_code, 
            stock_name,
            latest_report_date, 
            latest_notice_date, 
            next_report_expected
        )
        
        return {
            "success": True,
            "message": f"成功同步 {stock_name}({clean_code}) 财务数据 {records} 条",
            "records": records
        }
    except Exception as e:
        clean_code = clean_stock_code(stock_code)
        error_msg = f"财务数据处理失败: {e}"
        # 更新元数据表，记录错误信息
        update_finance_metadata_on_error(clean_code, stock_name, error_msg)
        return {
            "success": False,
            "message": error_msg,
            "records": 0
        }

@retry(max_attempts=5, delay=1, backoff=2, exceptions=(Exception,))
def get_stock_finance_data(symbol):
    """
    获取财务数据（带重试机制）
    """
    try:
        df = ak.stock_financial_analysis_indicator_em(
            symbol=symbol,
            indicator="按报告期"
        )
        return df
    except Exception as e:
        print(f"获取 {symbol} 财务数据异常: {e}")
        return pd.DataFrame()

def prepare_stock_finance_data(raw_df, stock_code, stock_name):
    """
    按 stock_finance 表字段要求预处理数据
    """
    # 只保留需要的列
    columns_to_keep = [
        'SECUCODE', 'SECURITY_CODE', 'SECURITY_NAME_ABBR', 'REPORT_DATE', 
        'REPORT_TYPE', 'REPORT_DATE_NAME', 'NOTICE_DATE', 'EPSJB', 'EPSKCJB', 
        'EPSXS', 'BPS', 'MGZBGJ', 'MGWFPLR', 'MGJYXJJE', 'TOTALOPERATEREVE', 
        'MLR', 'PARENTNETPROFIT', 'KCFJCXSYJLR', 'TOTALOPERATEREVETZ', 
        'PARENTNETPROFITTZ', 'KCFJCXSYJLRTZ', 'YYZSRGDHBZC', 'NETPROFITRPHBZC', 
        'KFJLRGDHBZC', 'ROEJQ', 'ROEKCJQ', 'ZZCJLL', 'XSJLL', 'XSMLL', 
        'XSJXLYYSR', 'LD', 'SD', 'XJLLB', 'ZCFZL', 'EPSJBTZ', 'BPSTZ', 
        'MGZBGJTZ', 'MGWFPLRTZ', 'MGJYXJJETZ', 'ROEJQTZ', 'ZZCJLLTZ', 
        'ZCFZLTZ', 'REPORT_YEAR', 'ROICTZ', 'DJD_TOI_YOY', 'DJD_DPNP_YOY', 
        'DJD_DEDUCTDPNP_YOY', 'DJD_TOI_QOQ', 'DJD_DPNP_QOQ', 
        'DJD_DEDUCTDPNP_QOQ', 'FIXED_ASSET_TR', 'CURRENT_ASSET_TR', 
        'GUARD_SPEED_RATIO', 'CASH_RATIO', 'CA_TA', 'NCA_TA', 
        'LIQUIDATION_RATIO', 'FC_LIABILITIES', 'FCFF_FORWARD', 'FCFF_BACK', 
        'SS_OI', 'LIABILITY'
    ]
    
    # 只保留存在的列
    available_columns = [col for col in columns_to_keep if col in raw_df.columns]
    filtered_df = raw_df[available_columns].copy()
    
    # 重命名列名为数据库字段名
    column_mapping = {
        'SECUCODE': 'secucode',  # 证券代码
        'SECURITY_CODE': 'security_code',  # 证券简码
        'SECURITY_NAME_ABBR': 'security_name_abbr',  # 证券简称
        'REPORT_DATE': 'report_date',  # 报告日期
        'REPORT_YEAR': 'report_year',  # 报告年份
        'REPORT_TYPE': 'report_type',  # 报告类型
        'REPORT_DATE_NAME': 'report_date_name',  # 报告期名称
        'NOTICE_DATE': 'notice_date',  # 公告日期
        'EPSJB': 'epsjb',  # 基本每股收益(元)
        'EPSKCJB': 'epskcjb',  # 扣非每股收益(元)
        'EPSXS': 'epsxs',  # 稀释每股收益(元)
        'BPS': 'bps',  # 每股净资产(元)
        'MGZBGJ': 'mgzbgj',  # 每股资本公积(元)
        'MGWFPLR': 'mgwfplr',  # 每股未分配利润(元)
        'MGJYXJJE': 'mgjyxjje',  # 每股经营现金流量净额(元)
        'TOTALOPERATEREVE': 'totaloperatereve',  # 营业总收入(元)
        'MLR': 'mlr',  # 毛利润(元)
        'PARENTNETPROFIT': 'parentnetprofit',  # 母公司净利润(元)
        'KCFJCXSYJLR': 'kcfjcxsyjlr',  # 扣非净利润(元)
        'TOTALOPERATEREVETZ': 'totaloperaterevetz',  # 营业收入同比增长率(%)
        'PARENTNETPROFITTZ': 'parentnetprofittz',  # 净利润同比增长率(%)
        'KCFJCXSYJLRTZ': 'kcfjcxsyjlrtz',  # 扣非净利润同比增长率(%)
        'YYZSRGDHBZC': 'yyzsrgdhbzc',  # 营业收入滚动环比增长(%)
        'NETPROFITRPHBZC': 'netprofitrphbzc',  # 净利润滚动环比增长(%)
        'KFJLRGDHBZC': 'kfjlrgdhbzc',  # 扣非净利润滚动环比增长(%)
        'ROEJQ': 'roejq',  # 净资产收益率(加权)(%)
        'ROEKCJQ': 'roekcjq',  # 扣非净资产收益率(加权)(%)
        'ZZCJLL': 'zzcjll',  # 总资产净利率(%)
        'XSJLL': 'xsjll',  # 销售净利率(%)
        'XSMLL': 'xsmll',  # 销售毛利率(%)
        'XSJXLYYSR': 'xsjxlyysr',  # 销售费用率(%)
        'LD': 'ld',  # 流动比率
        'SD': 'sd',  # 速动比率
        'XJLLB': 'xjllb',  # 现金流量比率
        'ZCFZL': 'zcfzl',  # 资产负债率(%)
        'EPSJBTZ': 'epsjbtz',  # 基本每股收益同比增长率(%)
        'BPSTZ': 'bpstz',  # 每股净资产同比增长率(%)
        'MGZBGJTZ': 'mgzbgjtz',  # 每股资本公积同比增长率(%)
        'MGWFPLRTZ': 'mgwfplrtz',  # 每股未分配利润同比增长率(%)
        'MGJYXJJETZ': 'mgjyxjjetz',  # 每股经营现金流量同比增长率(%)
        'ROEJQTZ': 'roejqtz',  # 净资产收益率同比增长率(%)
        'ZZCJLLTZ': 'zzcjlltz',  # 总资产净利率同比增长率(%)
        'ZCFZLTZ': 'zcfzltz',  # 资产负债率同比增长率(%)
        'ROICTZ': 'roictz',  # 投入资本回报率同比增长率(%)
        'DJD_TOI_YOY': 'djd_toi_yoy',  # 单季度营业收入同比增长率(%)
        'DJD_DPNP_YOY': 'djd_dpnp_yoy',  # 单季度净利润同比增长率(%)
        'DJD_DEDUCTDPNP_YOY': 'djd_deductdpnp_yoy',  # 单季度扣非净利润同比增长率(%)
        'DJD_TOI_QOQ': 'djd_toi_qoq',  # 单季度营业收入环比增长率(%)
        'DJD_DPNP_QOQ': 'djd_dpnp_qoq',  # 单季度净利润环比增长率(%)
        'DJD_DEDUCTDPNP_QOQ': 'djd_deductdpnp_qoq',  # 单季度扣非净利润环比增长率(%)
        'FIXED_ASSET_TR': 'fixed_asset_tr',  # 固定资产周转率
        'CURRENT_ASSET_TR': 'current_asset_tr',  # 流动资产周转率
        'GUARD_SPEED_RATIO': 'guard_speed_ratio',  # 保守速动比率
        'CASH_RATIO': 'cash_ratio',  # 现金比率
        'CA_TA': 'ca_ta',  # 流动资产占比(%)
        'NCA_TA': 'nca_ta',  # 非流动资产占比(%)
        'LIQUIDATION_RATIO': 'liquidation_ratio',  # 清算价值比率(%)
        'FC_LIABILITIES': 'fc_liabilities',  # 固定成本负债率(%)
        'FCFF_FORWARD': 'fcff_forward',  # 企业自由现金流(正向)(元)
        'FCFF_BACK': 'fcff_back',  # 企业自由现金流(反向)(元)
        'SS_OI': 'ss_oi',  # 销售费用与营业收入比(%)
        'LIABILITY': 'liability',  # 负债总额(元)
    }

    # 只重命名存在的列
    rename_mapping = {k: v for k, v in column_mapping.items() if k in filtered_df.columns}
    filtered_df = filtered_df.rename(columns=rename_mapping)
    
    # 处理日期字段
    date_columns = ['report_date', 'notice_date']
    for col in date_columns:
        if col in filtered_df.columns:
            filtered_df[col] = pd.to_datetime(filtered_df[col], errors='coerce').dt.date
    
    # 处理数值字段，将无穷大和NaN替换为None
    numeric_columns = filtered_df.select_dtypes(include=[np.number]).columns.tolist()
    for col in numeric_columns:
        filtered_df[col] = filtered_df[col].replace([np.inf, -np.inf], np.nan)
        # 对于空值或None值，不进行处理
        filtered_df[col] = filtered_df[col].where(pd.notnull(filtered_df[col]), None)
        
        # 只对非None值进行四舍五入
        if any(x in col for x in ['tz', 'ratio', 'rate', 'percent', 'pct', 'll', 'l']):
            filtered_df[col] = filtered_df[col].apply(lambda x: round(x, 6) if x is not None else None)
        # 对于金额字段，保留2位小数
        elif any(x in col for x in ['amount', 'revenue', 'profit', 'liability', 'cash', 'flow']):
            filtered_df[col] = filtered_df[col].apply(lambda x: round(x, 2) if x is not None else None)
        # 对于每股指标，保留4位小数
        elif col.startswith('eps') or col.startswith('mg') or col == 'bps':
            filtered_df[col] = filtered_df[col].apply(lambda x: round(x, 4) if x is not None else None)
    
    return filtered_df

@retry(max_attempts=3, delay=1, backoff=2, exceptions=(Exception,))
def insert_stock_finance_to_mysql(df):
    """
    插入或更新 MySQL - 财务数据表（支持幂等）
    """
    db_config = DBConfig.config
    engine = create_engine(
        f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}?charset={db_config['charset']}"
    )
    
    try:
        # 获取数据库连接
        with engine.begin() as conn:
            for idx, row in df.iterrows():
                # 构建字典参数
                row_dict = {k: (None if pd.isna(v) else v) for k, v in row.to_dict().items()}
                
                # 构建列和占位符
                columns = ', '.join(row_dict.keys())
                placeholders = ', '.join([f":{k}" for k in row_dict.keys()])
                
                # 构建更新部分
                update_clause = ', '.join([f"{col}=VALUES({col})" for col in row_dict.keys() if col not in ('secucode','report_date')])
                
                sql = f"""
                    INSERT INTO stock_finance ({columns})
                    VALUES ({placeholders})
                    ON DUPLICATE KEY UPDATE {update_clause}
                """
                
                conn.execute(text(sql), row_dict)
        
        return len(df)
    except Exception as e:
        print(f"插入/更新财务数据失败: {e}")
        raise
    finally:
        engine.dispose()

@retry(max_attempts=3, delay=1, backoff=2, exceptions=(Exception,))
def update_finance_metadata_on_success(stock_code, stock_name, last_report_date, last_announce_date, next_report_expected):
    """
    成功同步后更新finance_metadata表
    """
    db_config = DBConfig.config
    engine = create_engine(
        f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}?charset={db_config['charset']}"
    )
    
    try:
        with engine.begin() as conn:
            sql = """
                INSERT INTO finance_metadata (stock_code, stock_name, last_report_date, last_announce_date, next_report_expected, last_sync_time, last_error)
                VALUES (:stock_code, :stock_name, :last_report_date, :last_announce_date, :next_report_expected, NOW(), NULL)
                ON DUPLICATE KEY UPDATE 
                    stock_name = VALUES(stock_name),
                    last_report_date = VALUES(last_report_date),
                    last_announce_date = VALUES(last_announce_date),
                    next_report_expected = VALUES(next_report_expected),
                    last_sync_time = NOW(),
                    last_error = NULL
            """
            params = {
                'stock_code': stock_code,
                'stock_name': stock_name,
                'last_report_date': last_report_date,
                'last_announce_date': last_announce_date,
                'next_report_expected': next_report_expected
            }
            conn.execute(text(sql), params)
    except Exception as e:
        print(f"更新finance_metadata表失败: {e}")
        raise
    finally:
        engine.dispose()

@retry(max_attempts=3, delay=1, backoff=2, exceptions=(Exception,))
def update_finance_metadata_on_error(stock_code, stock_name, error_msg):
    """
    同步失败后更新finance_metadata表
    """
    db_config = DBConfig.config
    engine = create_engine(
        f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}?charset={db_config['charset']}"
    )
    
    try:
        with engine.begin() as conn:
            # 只更新错误信息，不更新其他字段
            sql = """
                INSERT INTO finance_metadata (stock_code, stock_name, last_error)
                VALUES (:stock_code, :stock_name, :error_msg)
                ON DUPLICATE KEY UPDATE 
                    stock_name = VALUES(stock_name),
                    last_error = VALUES(last_error)
            """
            params = {
                'stock_code': stock_code,
                'stock_name': stock_name,
                'error_msg': error_msg[:500]  # 确保错误信息不超过500字符
            }
            conn.execute(text(sql), params)
    except Exception as e:
        print(f"更新finance_metadata表失败: {e}")
        raise
    finally:
        engine.dispose()

# -------------------- 批量同步函数 --------------------
def batch_sync_stock_finance(stock_list):
    """
    批量同步股票财务数据
    :param stock_list: 股票列表，格式为 [(code, name), ...]
    """
    total_records = 0
    success_count = 0
    fail_count = 0
    
    for code, name in stock_list:
        try:
            result = sync_stock_finance_to_db(code, name)
            if result["success"]:
                success_count += 1
                total_records += result["records"]
                if result["records"] > 0:
                    print(f"成功同步 {name}({clean_stock_code(code)}) 财务数据 {result['records']} 条")
                else:
                    print(f"{name}({clean_stock_code(code)}) 无新数据可同步")
            else:
                fail_count += 1
                print(f"同步 {name}({clean_stock_code(code)}) 财务数据失败: {result['message']}")
        except Exception as e:
            fail_count += 1
            print(f"同步 {name}({clean_stock_code(code)}) 财务数据异常: {e}")
    
    # 打印统计信息
    print(f"\n同步完成统计:")
    print(f"成功同步股票数: {success_count}")
    print(f"失败股票数: {fail_count}")
    print(f"总记录数: {total_records}")

# 使用示例
if __name__ == "__main__":
    # 定义要同步的股票列表
    stock_list = [
        ("002236.SZ", "大华股份"),
        # ("600000.SH", "浦发银行"),
        # 可以添加更多股票
    ]
    
    # 批量同步
    batch_sync_stock_finance(stock_list)
    
    print('财务数据同步完成')